package com.wwwc.admin.web.database;

import java.util.*;
import java.sql.*;
import javax.sql.RowSet;
import java.io.*;
import org.hsqldb.*;
import com.mysql.jdbc.Driver;

public class Database implements Serializable {

    private String dataBaseName     = null; 
    private String dbDriverName     = null;
    private String userName         = null;
    private String password         = null;
    private Vector primaryKeysV     = null;

    private Vector tableRecordV     = new Vector ();
    private Vector tableColumnNameV = new Vector ();
    private Vector tableColumnTypeV = new Vector ();
    private Vector tableColumnSizeV = new Vector ();
    private StringBuffer messbf     = new StringBuffer();
  
    public String getSingleFieldValue(String query, String field) {
	Connection con = null;
	try {
	    //get the Class object associated with the class or interface with the given string 
	    Class.forName(dbDriverName);
	    //user Class java.sql.DriverManager through JDBC driver to connect db.
	    con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement stmt = con.createStatement();
	    //posiable options:
	    //createStatement(int resultSetType,int resultSetConcurrency)
	    //createStatement(int resultSetType,int resultSetConcurrency,int resultSetHoldability)

	    ResultSet res  = stmt.executeQuery(query);
	    //posiable options: null            
	    while (res.next()) {
		String s = res.getString(field);
		return s;
	    }

	    //RowSet rset = new RowSer();

	    //System.out.println(rset.getMaxFieldSize());
	    stmt.close();
	    con.commit();
	    con.close();
	} catch (java.lang.Exception ex) {
	    ex.printStackTrace();
	}
	return null;
    }

    public Vector getSingleRecord (String query) {
	Vector v = new Vector();
	int number_of_colums = 0;

	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement stmt = con.createStatement();
	    ResultSet res  = stmt.executeQuery(query);
	    
	    ResultSetMetaData rsmd = res.getMetaData();
	    number_of_colums = rsmd.getColumnCount();
	    while(res.next()) {
		v = getNextRow(res, rsmd);
		break;
	    }
	    stmt.close();
	    con.commit();
	    con.close();
	} catch (java.lang.Exception ex) {
	    System.out.println(ex);
	}
	return v;
    }

    public Vector getSingleFieldValueVector(String query, String field) {
	Vector v = new Vector();
	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement stmt = con.createStatement();
	    ResultSet res  = stmt.executeQuery(query);
	    while (res.next()) {
		v.add(res.getString(field));
	    }
	    stmt.close();
	    con.commit();
	    con.close();
	} catch (java.lang.Exception ex) {
	    ex.printStackTrace();
	}
	return v;
    }

    public Hashtable getTwoFieldsHashtable(String query, String field1, String field2) {
	Hashtable twoFieldsHash = new Hashtable();
	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement stmt = con.createStatement();
	    ResultSet res  = stmt.executeQuery(query);
	    while (res.next()) {
		twoFieldsHash.put(""+res.getString(field1), ""+res.getString(field2));
	    }
	    stmt.close();
	    con.commit();
	    con.close();
	} catch (SQLException e) {
	    System.out.println(e);
	}
	catch (Exception e) {
	    System.out.println(e);
	}
	return twoFieldsHash;
    }

    public int getRowCounter ( String table_name) {
	int result = -1;
	String query = "SELECT COUNT(*) AS rowcount FROM "+table_name+";";
	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement stmt = con.createStatement();
	    ResultSet rs = stmt.executeQuery(query);
	    if(rs.next()) {
		result = rs.getInt("rowcount");
	    }
	    rs.close();
	    stmt.close();
	    con.close();
	}
	catch (SQLException e) {
	    System.out.println("Database:getRowCounter:Error["+e+"]");
	} catch (java.lang.Exception e) {
	    System.out.println("Database:getRowCounter:Error["+e+"]");
	}
	return result;
    }

    public int getNextProductId () {
	int result = -1;
	String query = "SELECT MAX(ID) AS next_id FROM PRODUCT;";
	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement stmt = con.createStatement();
	    ResultSet rs = stmt.executeQuery(query);
	    if(rs.next()) {
		result = rs.getInt("next_id");
	    }
	    result = result+1;
	    rs.close();
	    stmt.close();
	    con.close();
	}
	catch (SQLException e) {
	    System.out.println("Database:getRowCounter:Error["+e+"]");
	} catch (java.lang.Exception e) {
	    System.out.println("Database:getRowCounter:Error["+e+"]");
	}
	return result;
    }

    public int insert(String query) {
	//if ok return >0
	int result = 0;
	
	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement statement = con.createStatement();

	    result = statement.executeUpdate(query);
	    statement.close();
	    con.commit();
	    con.close();
	}
	catch (SQLException sqlex ) {
	    messbf.append("SQLException["+sqlex.toString()+"]<BR>");
	    System.out.println("Database:insert:Error["+sqlex.toString()+"]");
	} catch (java.lang.Exception ex) {
	    messbf.append("Exception["+ex.toString()+"]<BR>");
	    System.out.println("Database:insertDate:Error["+ex.toString()+"]");
	}
	return result;
    }

    public int update ( String query) {
	int result = 0;
	Vector colum_heads = new Vector ();
	Vector rows        = new Vector ();
	int number_of_colums = 0;

	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement stmt = con.createStatement();
	    result=stmt.executeUpdate(query);
	    stmt.close();
	    //con.commit();
	    con.close();
	}
	catch (SQLException sqlex ) {
	    result = -1;
	    messbf.append("SQLException["+sqlex.toString()+"]<BR>");
	    System.out.println("Database:update:Error["+sqlex.toString()+"]");
	} catch (java.lang.Exception ex) {
	    result = -1;
	    messbf.append("Exception["+ex.toString()+"]<BR>");
	    System.out.println("Database:update:Error["+ex.toString()+"]");
	}
	return result;
    }

    public StringBuffer insert(String table_name, Vector vrh, Vector vrv) {
	StringBuffer qsbf = null;
	Vector vh = getColumnNames(table_name);
	Vector vt = getColumnTypeNames(table_name);
	Connection con = null;
	int rs = -1;
	try {	
	    Class.forName(dbDriverName);
	    con = DriverManager.getConnection(dataBaseName, userName, password);
	    
	    qsbf = new StringBuffer("INSERT INTO "+table_name+"(");
	    Enumeration enum = vrh.elements();
	    while(enum.hasMoreElements()) {
		qsbf.append(enum.nextElement() +",");
	    }
	    
	    String temp = qsbf.toString();
	    qsbf = new StringBuffer(temp.substring(0, temp.length()-1));
	    qsbf.append(") VALUES(");
	    enum = vrv.elements();
	    while(enum.hasMoreElements()) {
		enum.nextElement();
		qsbf.append("?,");
	    }

	    temp = qsbf.toString();
	    qsbf = new StringBuffer(temp.substring(0, temp.length()-1)+");");

	    PreparedStatement prepStmt =  con.prepareStatement(qsbf.toString());

	    int index = -1;
	    String type = null;
	    enum = vrh.elements();
	    int m = 1;
	    while (enum.hasMoreElements()) {
		index = vh.indexOf(enum.nextElement());

		if(index <0) {
		    return (new StringBuffer("Column ["+temp+"] not found."));
		}

		type =  (String)vt.elementAt(index);

		if(type.startsWith("VARCHAR") || type.equals("TIMESTAMP") || type.equals("DATE")) {
		    prepStmt.setString(m, ""+vrv.elementAt(m-1));
		}
		else if(type.startsWith("INT") || type.equals("SMALLINT") || type.equals("NUMERIC")) {
		    prepStmt.setInt(m, Integer.parseInt((String)vrv.elementAt(m-1)));
		}
		else if(type.equals("FLOAT") || type.equals("DOUBLE") || type.equals("DECIMAL")) {
		    prepStmt.setDouble(m, Double.parseDouble("0"+vrv.elementAt(m-1)));
		}
		else if(type.equals("BINARY")) {
		    prepStmt.setObject(m, vrv.elementAt(m-1));
		}
		else if(type.equals("BIT")) {
		    prepStmt.setBoolean(m, (Boolean.valueOf(""+vrv.elementAt(m-1))).booleanValue());
		}
		else {
		    qsbf.append("<BR>Database:update:ColumnType["+type+"] not found 201");
		    return qsbf;
		}
		m++;
	    }

	    rs = prepStmt.executeUpdate();
	    prepStmt.close();
	    
	    con.close();
	    if(rs > 0) {
		qsbf=null;
	    }
	}
	catch(SQLException se) {
	    rollback(con);
	    releaseConnection(con);
	    return qsbf.append("<BR>Database:inset(table_name, vr):SQLException=<BR>"+se);
	}
	catch(Exception e) {
	    rollback(con);
	    releaseConnection(con);
	    return qsbf.append("<BR>BBBBDatabase:insert(table_name,vr):Exception=<BR>"+e);
	}
	return qsbf;
    }
    
    public StringBuffer update(String table_name, Vector vro, Vector vrh, Vector vrv) {
	StringBuffer qsbf = null;
	Vector vh = getColumnNames(table_name);
	Vector vt = getColumnTypeNames(table_name);
	Vector vk = getTablePrimaryKeysV(table_name);

	Connection con = null;
	int rs = -1;
	try {	
	    Class.forName(dbDriverName);
	    con = DriverManager.getConnection(dataBaseName, userName, password);

	    //==================================================================
	    // make query string 
	    //==================================================================	
    
	    qsbf = new StringBuffer("UPDATE "+table_name+" SET ");
	    Enumeration enum = vrh.elements();

	    while(enum.hasMoreElements()) {
		qsbf.append(enum.nextElement() +"=?,");
	    }

	    String temp = qsbf.toString();
	    qsbf = new StringBuffer(temp.substring(0, temp.length()-1));
	    
	    // where clouse for table has key

	    int j=0;
	    enum = vk.elements();
	    while(enum.hasMoreElements()) {
		if(j==0) {
		    qsbf.append(" WHERE ");
		}
		qsbf.append(" "+enum.nextElement()+"=? AND");
		j++;
	    }

	    // if no key, use all column names == old values

	    if(j==0) {
		int n = 0;
		enum = vrh.elements();
		while(enum.hasMoreElements()) {
		    if(n==0) {
			qsbf.append(" WHERE ");
		    }
		    qsbf.append(" "+enum.nextElement()+"=? AND");
		    n++;
		}
	    }

	    temp = qsbf.toString();

	    if(temp.lastIndexOf("AND") != -1) {
		qsbf = new StringBuffer(temp.substring(0, temp.lastIndexOf("AND")));
	    }
	    else {
		return qsbf.append(";");
	    }

	    qsbf.append(";");
	   
	    PreparedStatement prepStmt =  con.prepareStatement(qsbf.toString());

	    //==================================================================
	    // set value for each "?" in query string
	    //==================================================================

	    // set columns "?" value

	    int m =1;
	    int idx_h = -1;

	    enum = vrh.elements();
	    while (enum.hasMoreElements()) {
		idx_h = vh.indexOf(enum.nextElement());
		temp  = (String)vt.elementAt(idx_h);

		if(temp.startsWith("VARCHAR") || temp.equals("TIMESTAMP") || temp.equals("DATE")) {
		    prepStmt.setString(m, ""+vrv.elementAt(m-1));
		}
		else if(temp.startsWith("INT") || temp.equals("SMALLINT") ) {
		    prepStmt.setInt(m, Integer.parseInt((String)vrv.elementAt(m-1)));
		}
		else if(temp.equals("BIGINT")) {
		    prepStmt.setLong(m, Long.parseLong((String)vrv.elementAt(m-1)));
		}
		else if(temp.equals("DOUBLE") || temp.equals("FLOAT") || temp.equals("DECIMAL") || temp.equals("NUMERIC")) {
		    prepStmt.setDouble(m, Double.parseDouble((String)vrv.elementAt(m-1)));
		}
		else if(temp.equals("BINARY")) {
		    prepStmt.setObject(m, vrv.elementAt(m-1));
		}
		else if(temp.equals("BIT")) {
		    prepStmt.setBoolean(m, (Boolean.valueOf((String)vrv.elementAt(m-1))).booleanValue());
		}
		else {
		    System.out.println("Database:update:ColumnType["+temp+"] not found 202");
		    return qsbf;
		}
		m++;
	    }

	    // set where clouse "?" value 

	    int key_index = -1;
	    String key_type = null;
	    enum = vk.elements();
	    while(enum.hasMoreElements()) {
		System.out.println("Table has key");
		key_index = vh.indexOf(enum.nextElement());
		temp = (String)vt.elementAt(key_index);
		if(temp.startsWith("VARCHAR") || temp.equals("TIMESTAMP")) {
		    prepStmt.setString(m, (String)vro.elementAt(key_index));
		}
		else if(temp.startsWith("INT") || temp.equals("BIT") || temp.equals("SMALLINT")) {
		    prepStmt.setInt(m, Integer.parseInt((String)vro.elementAt(key_index)));
		}
		else if(temp.equals("BIGINT")) {
		    prepStmt.setLong(m, Long.parseLong((String)vro.elementAt(key_index)));
		}
		else if(temp.equals("NUMERIC") || temp.equals("FLOAT")) {
		    prepStmt.setDouble(m, Double.parseDouble((String)vro.elementAt(key_index)));
		}
		else {
		    System.out.println("<BR>Database:update:ColumnType["+temp+"] not found in key element[101]");
		    return qsbf;
		}
		m++;
	    }

	    // where cluse for table has no key

	    if(key_index == -1) {
		System.out.println("Table has no key");
		enum = vrh.elements();
		while(enum.hasMoreElements()) {
		    idx_h = vh.indexOf(enum.nextElement());
		    temp  = (String)vt.elementAt(idx_h);

		    if(temp.startsWith("VARCHAR") || temp.equals("TIMESTAMP") || temp.equals("DATE")) {
			prepStmt.setString(m, ""+vro.elementAt(idx_h));
		    }
		    else if(temp.startsWith("INT") || temp.equals("SMALLINT")) {
			prepStmt.setInt(m, Integer.parseInt((String)vro.elementAt(idx_h)));
		    }
		    else if(temp.equals("NUMERIC") || temp.equals("FLOAT")) {
			prepStmt.setDouble(m, Double.parseDouble((String)vro.elementAt(idx_h)));
		    }
		    else if(temp.equals("BIGINT")) {
			prepStmt.setLong(m, Long.parseLong((String)vro.elementAt(key_index)));
		    }
		    else {
			System.out.println("Database:update:ColumnType["+temp+"] not found in key elements[102].");
			return qsbf;
		    }
		    m++;
		}
	    }

	    rs = prepStmt.executeUpdate();
	    prepStmt.close();
	    con.close();
	    if(rs > 0) {
		qsbf=null;
	    }
	}
	catch(SQLException se) {
	    rollback(con);
	    releaseConnection(con);
	    System.out.println("Database:update(v, v):SQLException="+se);
	    return qsbf.append("Database:update(v, v):SQLException="+se);
	}
	catch(Exception e) {
	    rollback(con);
	    releaseConnection(con);
	    System.out.println("<BR>Database:update(v,v):Exception="+e);
	    return qsbf.append("<BR>Database:update(v,v):Exception="+e);
	}
	return qsbf;
    }

    public StringBuffer delete (String table_name, Vector vro) {
	Vector vh = getColumnNames(table_name);
	Vector vt = getColumnTypeNames(table_name);
	Vector vk = getTablePrimaryKeysV(table_name);

	Connection con = null;
	StringBuffer qsbf = null;
	int rs = -1;

	try {	
	    //==================================================================
	    // make query string 
	    //==================================================================	
	    
	    String temp = null;
	    qsbf = new StringBuffer ("DELETE FROM "+table_name+" ");

	    int idx_h = -1;
	    int j=0;
	    Enumeration enum = vk.elements();
	    while(enum.hasMoreElements()) {
		if(j==0) {
		    qsbf.append(" WHERE ");
		}
		
		qsbf.append(" "+enum.nextElement()+"=? AND");
		j++;
	    }

	    // if no key, use all column names == old values

	    String value = null;
	    String name = null;
	    if(j==0) {
		int n = 0;
		enum = vh.elements();
		qsbf.append(" WHERE ");
		while(enum.hasMoreElements()) {
		    name = (String)enum.nextElement();
		    temp = (String)vt.elementAt(n);
		    if(temp.startsWith("VARCHAR")) {
			value = (String)vro.elementAt(n);
			if(value != null && value.length()>0) {
			    qsbf.append(" "+name+"='"+value+"' AND");
			}
		    }
		    else if(temp.startsWith("INT")) {
			value = (String)vro.elementAt(n);
			if(value != null && value.length()>0) {
			    qsbf.append(" "+name+"="+value+" AND");
			}
		    }
		    n++;
		}
	    }

	    temp = qsbf.toString();
	    if(temp.lastIndexOf("AND") != -1) {
		qsbf = new StringBuffer(temp.substring(0, temp.lastIndexOf("AND")));
	    }

	    qsbf.append(";");

	    if(j>0) { // table has key
		Class.forName(dbDriverName);
		con = DriverManager.getConnection(dataBaseName, userName, password);
		PreparedStatement prepStmt =  con.prepareStatement(qsbf.toString());
		//=================================================================
		// set where clouse "?" value 
		//=================================================================

		int key_index = -1;
		int m = 1;
		enum = vk.elements();
		while(enum.hasMoreElements()) {
		    key_index = vh.indexOf(enum.nextElement());
		    temp = (String)vt.elementAt(key_index);
		    if(temp.startsWith("VARCHAR") || temp.equals("TIMESTAMP")) {
			prepStmt.setString(m, (String)vro.elementAt(key_index));
		    }
		    else if(temp.startsWith("INT") || temp.equals("BIT") || temp.equals("SMALLINT")) {
			prepStmt.setInt(m, Integer.parseInt((String)vro.elementAt(key_index)));
		    }
		    else if(temp.equals("NUMERIC") || temp.equals("FLOAT")) {
			prepStmt.setDouble(m, Double.parseDouble((String)vro.elementAt(key_index)));
		    }
		    else if(temp.equals("BIGINT")) {
			prepStmt.setLong(m, Long.parseLong((String)vro.elementAt(key_index)));
		    }
		    else {
			System.out.println("<BR>Database:update:ColumnType["+temp+"] not found in key element[110]");
			return qsbf;
		    }
		    m++;
		}
		rs = prepStmt.executeUpdate();
		prepStmt.close();
		con.close();
	    }
	    else {
		// excute query for table has no key
		rs = delete(qsbf.toString());
	    }

	    if(rs > 0) {
		qsbf=null;
	    }
	}
	catch(SQLException se) {
	    rollback(con);
	    releaseConnection(con);
	    System.out.println("Database:delete(table, v):SQLException="+se);
	    return qsbf.append("Database:delete(table, v):SQLException="+se);
	}
	catch(Exception e) {
	    rollback(con);
	    releaseConnection(con);
	    System.out.println("<BR>Database:delete(table,v):Exception="+e);
	    return qsbf.append("<BR>Database:delete(table,v):Exception="+e);
	}

	return qsbf;
    }

    public StringBuffer delete(String table_name, String key_name, String key_type, String key_value) {
	StringBuffer qsbf = new StringBuffer("DELETE FROM "+table_name+" WHERE "+key_name+"=");
	String temp = key_type;
	if(temp.startsWith("VARCHAR") || temp.equals("TIMESTAMP")) {
	    qsbf.append("'"+key_value+"';");
	}
	else if(temp.startsWith("INT") || temp.equals("SMALLINT")) {
	    qsbf.append(""+key_value+";");
	}
	else if(temp.equals("NUMERIC") || temp.equals("FLOAT")) {
	    qsbf.append(""+key_value+";");
	}
	else {
	    qsbf.append("Database:delete(k,k,k):ColumnType["+temp+"] not found");
	    return qsbf;
	}
	
	if(delete(qsbf.toString())>0) {
	    qsbf=null;
	}
	return qsbf;
    }

    public String removeTable(String table_name) {
	String query = "ALTER TABLE "+table_name+" RENAME TO DELETE_"+table_name+";";
	if(update(query) <0) {
	    return "Database:removeTable:Error:["+query+"]";
	}
	return null;
    }

    public String deleteTable(String table_name) {
	String query = "DROP TABLE "+table_name+";";
	if(update(query)<0) {
	    return "Database:deleteTable:Error:["+query+"]";
	}
	return null;
    }

    public int delete ( String query) {
	int result = 0;
	Vector colum_heads = new Vector ();
	Vector rows        = new Vector ();
	int number_of_colums = 0;

	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement stmt = con.createStatement();
	    result=stmt.executeUpdate(query);
	    stmt.close();
	    con.commit();
	    con.close();
	}
	catch (SQLException sqlex ) {
	    messbf.append("SQLException["+sqlex.toString()+"]<BR>");
	    System.out.println("Database:delete:Error["+sqlex.toString()+"]");
	} catch (java.lang.Exception ex) {
	    messbf.append("Exception["+ex.toString()+"]<BR>");
	    System.out.println("Database:delete:Error["+ex.toString()+"]");
	}
	return result;
    }

    public int select(String query) {
	Vector colum_name = new Vector ();
	Vector colum_type = new Vector ();
	Vector colum_size = new Vector ();
	Vector rows       = new Vector ();

	int number_of_colums = 0;
	int result = 0;
	try {
	    Class.forName(dbDriverName).newInstance(); 
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement stmt = con.createStatement();
	    
	    ResultSet res  = stmt.executeQuery(query);
	    
	    ResultSetMetaData rsmd = res.getMetaData();
	    number_of_colums = rsmd.getColumnCount();
	    
	    for (int i=1; i <= number_of_colums; ++i) {
		colum_name.add(rsmd.getColumnName(i));
		colum_type.add(rsmd.getColumnTypeName(i));
		colum_size.add(""+rsmd.getColumnDisplaySize(i));
	    }
		
	    while (res.next()){
		rows.addElement(getNextRow(res, rsmd));
	    }
	    
	    setTableRecordVector(rows);
	    setTableColumnNameVector(colum_name);
	    setTableColumnTypeVector(colum_type);
	    setTableColumnSizeVector(colum_size);
	    stmt.close();
	    con.close();
	} catch (SQLException e) {
	    System.out.println("Database:select:Error:"+e);
	    result = e.getErrorCode();
	    messbf.append("Database:select:SQLException["+e+"]");
	} 
	catch (Exception e) {
	    System.out.println("Database:select:Error:"+e);
	    result = -1;
	    messbf.append("Database:select:Exception["+e+"]");
	}
	return result;
    }

    public Vector getColumnNames(String table_name){
	Connection con = null;
	String query = "SELECT LIMIT 0 1 * FROM "+table_name;
	Vector v = new Vector();

	try {	
	    Class.forName(dbDriverName);
	    con = DriverManager.getConnection(dataBaseName, userName, password);
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colums = rsmd.getColumnCount();

	    for(int m = 1; m <= colums; m++) {
		v.add(rsmd.getColumnName(m));
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection(con);
	}
	catch(SQLException e) {
	    releaseConnection(con);
	    System.out.println("Database:getColumnNames:Error"+e);
	}
	catch(Exception e) {
	    releaseConnection(con);
	    System.out.println("Database:getColumnNames:Error:"+e);
	}
        return v;
    }

    public Vector getColumnTypeNames(String table_name){
	Connection con = null;
	String query = "SELECT LIMIT 0 1 * FROM "+table_name;
	Vector v = new Vector();

	try {	
	    Class.forName(dbDriverName);
	    con = DriverManager.getConnection(dataBaseName, userName, password);
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colums = rsmd.getColumnCount();

	    for(int m = 1; m <= colums; m++) {
		v.add(rsmd.getColumnTypeName(m));
	    }

	    rs.close();
	    prepStmt.close();
	    releaseConnection(con);
	}
	catch(SQLException e) {
	    releaseConnection(con);
	    System.out.println("Database:getColumnTypeNames:Error"+e);
	}
	catch(Exception e) {
	    releaseConnection(con);
	    System.out.println("Database:getColumnTypeNames:Error:"+e);
	}
        return v;
    }

    public Vector getTableNameV (String database) {
	Vector v = new Vector();
	if(database == null) {
	    return v;
	}
	String query = null;
	String name = null;
	if(database.equals("Access")) {
	    name = "Name";
	    query = "SELECT Name FROM MSysObjects WHERE Name Not Like 'MSys%' AND (Type=1 Or Type=6) ORDER BY Name; ";
	}
	else if(database.startsWith("Hsqldb")) {
	    name = "TABLE_NAME";
	    query = "SELECT TABLE_NAME FROM SYSTEM_TABLES ORDER BY TABLE_NAME;";
	}
	if(query==null) {
	    return v;
	}
	
	return(this.getSingleFieldValueVector(query, name));
    }

    public void select(String query, String table_name, String database) {
	Vector colum_name = new Vector ();
	Vector colum_type = new Vector ();
	Vector colum_size = new Vector ();
	Vector rows       = new Vector ();

	int number_of_colums = 0;

	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement stmt = con.createStatement();

	    if(database.startsWith("Hsql") || database.startsWith("MySql")) {
		DatabaseMetaData dbmt = con.getMetaData();
		ResultSet resk = dbmt.getPrimaryKeys(null,null,table_name);
		int n=0;
		primaryKeysV = new Vector();
		while(resk.next()) {
		    primaryKeysV.add(resk.getObject(4));
		    n++;
		}	    
	    }

	    if(database.equals("Access")) { //MS-Access doesnot support this function, just return a empty primaryKeysV
		DatabaseMetaData dbmt = con.getMetaData();
		primaryKeysV = new Vector();
	    }

	    ResultSet res  = stmt.executeQuery(query);

	    ResultSetMetaData rsmd = res.getMetaData();
	    number_of_colums = rsmd.getColumnCount();
	   
	    for (int i=1; i <= number_of_colums; ++i) {
		colum_name.add(rsmd.getColumnName(i));
		colum_type.add(rsmd.getColumnTypeName(i));
		colum_size.add(""+rsmd.getColumnDisplaySize(i));
	    }
	  
	    while (res.next()){
		rows.addElement(getNextRow(res, rsmd));
	    }

	    setTableRecordVector(rows);
	    setTableColumnNameVector(colum_name);
	    setTableColumnTypeVector(colum_type);
	    setTableColumnSizeVector(colum_size);
	    stmt.close();
	    con.commit();
	    con.close();
	} catch (java.lang.Exception ex) {
	    System.out.println("Database.select:[query="+query+"][table_name="+ table_name+"][database="+ database+"][Error]["+ex+"]");
	}
    }

    public Vector getDatabaseInfo () {
	Vector v = new Vector();
	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    DatabaseMetaData dbmt = con.getMetaData();
	    //============================================================================
	    // (MS-Access does not support)
	    //============================================================================
	    System.out.println("getDatabaseProduceName="+dbmt.getDatabaseProductName());
	    //============================================================================
	    System.out.println("getDatabaseProduceVersion="+dbmt.getDatabaseProductVersion());
	    //============================================================================
	    System.out.println("getDriverVersion="+dbmt.getDriverName());
	    //============================================================================
	    System.out.println("getDriverVersion="+dbmt.getDriverVersion());
	}
	catch (java.lang.Exception ex) {
	    System.out.println("Database.getDatabaseInfo:[Error]["+ex+"]");
	}
	return v;
    }

    public void setTableInfo (String table_name) {
	Hashtable vhs = new Hashtable ();
	try {
	    String database = this.getDataBaseName();
	    if(database == null) {
		System.out.println("Database:getTableInfo:Error");
		return;
	    }

	    String query = null;
	    
	    if(database.equals("Access")) {
		query = "SELECT * FROM "+table_name+";";
	    }
	    else {
		query = "SELECT LIMIT 0 1 * FROM "+table_name +";";
	    }
	    this.select(query, table_name, database);
	}
	catch (Exception e) {
	    System.out.println("Database:getTableInfo:Error:"+e);
	}
    }

    public Vector getTablePrimaryKeysV (String table_name) {
	Vector v = new Vector();
	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    DatabaseMetaData dbmt = con.getMetaData();
	    ResultSet resk = dbmt.getPrimaryKeys(null,null,table_name);

	    v = new Vector();
	    while(resk.next()) {
		v.add(resk.getObject(4));
	    }
	    
	    //System.err.println("TABLE_CAT  : "+resk.getObject(1));
	    //System.err.println("TABLE_SCHEM: "+resk.getObject(2));
	    //System.err.println("TABLE_NAME : "+resk.getObject(3));
	    //System.err.println("COLUMN_NAME: "+resk.getObject(4));
	    //System.err.println("KEY_SEQ    : "+resk.getObject(5));
	    //System.err.println("PK_NAME    : "+resk.getObject(6));

	    con.close();
	} catch (java.lang.Exception ex) {
	    System.out.println("Database.getTablePrimaryKeysV:[Error]["+ex+"]");
	}
	return v;
    }

    public ArrayList excute ( String query) {
	int result = 0;
	Vector colum_heads = new Vector ();
	Vector rows        = new Vector ();
	int number_of_colums = 0;
	ArrayList a = new ArrayList();
	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    PreparedStatement prepStmt =  con.prepareStatement(query);
	    ResultSet rs = prepStmt.executeQuery();
	    ResultSetMetaData rsmd = rs.getMetaData();
	    int colums = rsmd.getColumnCount();

	    while (rs.next()) {
		for(int m = 1; m <= colums; m++) {
		    a.add(rs.getString(m)+"");
		}
	    }
   
	    prepStmt.close();
	    //con.commit();
	    con.close();
	
	}
	catch (SQLException sqlex ) {
	    messbf.append("SQLException["+sqlex.toString()+"]<BR>");
	    System.out.println("Database:update:Error["+sqlex.toString()+"]");
	} catch (java.lang.Exception ex) {
	    messbf.append("Exception["+ex.toString()+"]<BR>");
	    System.out.println("Database:update:Error["+ex.toString()+"]");
	}
	return a;
    }

    public int createTable(String query) {
	int result = -1; //if ok, return 0
	query = "SET IGNORECASE FALSE;"+query; 
	try {
	    Class.forName(dbDriverName);
	    Connection con = DriverManager.getConnection(dataBaseName, userName, password);
	    Statement statement = con.createStatement();

	    //output.append( "\nSending query: " +  con.nativeSQL( query ) + "\n");
	    result = statement.executeUpdate( query);
	    System.out.println("result="+result);
	    statement.close();
	    con.commit();
	    con.close();
	}
	catch (SQLException e) {
	    messbf.append("SQLException["+e.toString()+"]\n<BR>");	
	    System.out.println("Database:createTable:Error:"+messbf);
    
	} catch (Exception e) {
	    messbf.append("Exception["+e.toString()+"]\n<BR>");
	    System.out.println("Database:createTable:Error:"+messbf);
	}
	return result;
    }

    public String driverInfo () {
	StringBuffer drbf = new StringBuffer();
	try {
	    Enumeration enum = DriverManager.getDrivers();
	    while(enum.hasMoreElements()) {
		drbf.append("["+enum.nextElement()+"]");
	    }	    
	} catch (java.lang.Exception ex) {
	    drbf.append("Error:"+ex);
	}
	return(drbf.toString());
    }

    private Vector getNextRow( ResultSet rset,  ResultSetMetaData rsetmd )
    {
	Vector currentRow = new Vector();
	String temp   = null;

	try {
	    for ( int i = 1; i <= rsetmd.getColumnCount(); ++i ) {
		switch( rsetmd.getColumnType(i)) {
		case Types.VARCHAR:
		    temp = rset.getString(i);
		    if(temp == null) {
			temp ="";
		    }
		    currentRow.addElement(temp);
		    break;
		case Types.INTEGER:
		case Types.SMALLINT:
		    currentRow.addElement(""+new Long(rset.getLong(i)));
		    break;
		default:
		    temp = rset.getString(i);
		    if(temp == null) {
			temp ="";
		    }
		    currentRow.addElement(temp);
		    break;
		}
	    }
	}
	catch (SQLException e) {
	    System.out.println("getNextRow=["+e+"]");
	}
	return currentRow;
    }

    private void releaseConnection( Connection con) {
        try {
	    if(con != null) {
		con.close();
	    }
        } catch (SQLException e) {
	    System.out.println("Database:releaseConnection:SQLException:"+e);
        }
    } 

    private void rollback(Connection con) {
	try {	
	    if(con == null) {
		Class.forName(dbDriverName);
		con = DriverManager.getConnection(dataBaseName, userName, password);
	    }
	    PreparedStatement prepStmt =  con.prepareStatement("ROLLBACK");
	    prepStmt.executeUpdate();
	    prepStmt.close();
	    con.close();
	}
	catch(SQLException e) {
	    releaseConnection(con);
	    System.out.println("Rollback SQLException="+e);
	}
	catch(Exception e) {
	    releaseConnection(con);
	    System.out.println("Rollback Exception="+e);
	}
    }

    public void setDataBaseName (String dbname) {
	dataBaseName = dbname;
    }

    public String getDataBaseName () {
	return dataBaseName;
    }

    public void setDriverName (String drname) {
	dbDriverName = drname;
    }

    public void setUserName (String user_name) {
	userName = user_name;
    }

    public void setPassword (String psword) {
	password = psword;
    }

    public Vector getTablePrimaryKeysVector() {
	return primaryKeysV;
    }

    public void setTableColumnNameVector(Vector cv) {
	tableColumnNameV = cv;
    }

    public Vector getTableColumnNameVector() {
	return tableColumnNameV;
    }

    public void setTableColumnTypeVector(Vector ctv) {
	tableColumnTypeV = ctv;
    }

    public Vector getTableColumnTypeVector() {
	return tableColumnTypeV;
    }

    public void setTableColumnSizeVector(Vector szv) {
	tableColumnSizeV = szv;
    }

    public Vector getTableColumnSizeVector() {
	return tableColumnSizeV;
    }

    public void setTableRecordVector(Vector table_record_v) {
	tableRecordV = table_record_v;
    }
    public Vector getTableRecordVector() {
	return tableRecordV;
    }
    public String getMessage() {
	return messbf.toString();
    }
}

